﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;
using System.Security.Cryptography;
using System.Data.SqlClient;
using System.Data;
using System.Diagnostics;
using System.Windows.Forms;
namespace GPRSForm.Database
{
    class DataBase
    {
        #region //declare variable
        public MySqlConnection connection;
        public string connectionString;
        MySqlDataAdapter adapter;
        #endregion
        public DataBase()
        {
            Initialize();
            adapter = new MySqlDataAdapter("", connection);
            DataTable d = new DataTable();
        }
        public void Initialize()
        {

            connectionString = "SERVER=" + DBConstant.HOST + ";" + "DATABASE=" + DBConstant.DATABASE + ";"
                                + "UID=" + DBConstant.USERNAME + ";" + "PASSWORD=" + DBConstant.PASSWORD + ";";
            try
            {
                connection = new MySqlConnection(connectionString);
                OpenConnection();
            }
            catch (MySqlException e)
            {
                System.Diagnostics.EventLog.WriteEntry("SMS", e.Message, EventLogEntryType.Error);
                MessageBox.Show(e.Message);
            }
        }
        //Open connection
        private bool OpenConnection()
        {
            try
            {
                connection.Open();
                return true;
            }
            catch (MySqlException ex)
            {
                System.Diagnostics.EventLog.WriteEntry("SMS", ex.Message, EventLogEntryType.Error);
                MessageBox.Show(ex.Message);
            }
            return false;
        }
        //Close connection
        private bool CloseConnnection()
        {
            try
            {
                connection.Close();
                return true;
            }
            catch (MySqlException ex)
            {
                System.Diagnostics.EventLog.WriteEntry("SMS", ex.Message, EventLogEntryType.Error);
                MessageBox.Show(ex.Message);
            }
            return false;
        }
        //ExcuteNonQuery Insert and update or delete
        public bool ExcuteNonQuerry(string query)
        {
            try
            {
                if (connection == null)
                {
                    this.OpenConnection();
                }
                // create command
                MySqlCommand cmd = connection.CreateCommand();
                // add querry text and connection
                cmd.CommandText = query;       
                //excute
                cmd.ExecuteNonQuery();
                //cmd.ex
                return true;
                //this.CloseConnnection();
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
                return false;
            }
        }
        //Check select query
        public bool checkQuery(string query)
        {
            try
            {
                MySqlCommand cmd = new MySqlCommand(query, connection);
                MySqlDataReader reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    reader.Close();
                    return true;
                }
                else
                {
                    reader.Close();
                    return false;
                }

            }
            catch (MySqlException ex)
            {
                System.Diagnostics.EventLog.WriteEntry("SMS", ex.Message, EventLogEntryType.Error);
                //return false;
            }
            return false;
        }

        public DataTable getSMS()
        {
            DataSet dataset = null;
            try
            {
                adapter.SelectCommand.CommandText = "SELECT * FROM 9_sms WHERE is_sent=" + 0;
                dataset = new DataSet();
                adapter.Fill(dataset);
                
            }
            catch (Exception e)
            {
                System.Diagnostics.EventLog.WriteEntry("SMS", e.Message, EventLogEntryType.Error);
                MessageBox.Show(e.Message);
            }
            return dataset.Tables[0];
        }
        public bool updateSMS(string id,double date)
        {
            string query = "UPDATE 9_sms SET is_sent ='1',date_send='"+ date + "' WHERE sms_id=" + id;
            if (ExcuteNonQuerry(query))
                return true;
            else return false;
        }
    }
}
